sum over partition by 的用法 | 您所在的位置:网站首页 › partition by case when › sum over partition by 的用法 |
--用法详解
0、select * from wmg_test; ---测试数据 1、select v1,v2,sum(v2) over(order by v2) as sum --按照 v2排序,累计n+n-1+....+1 from wmg_test;
2、select v1,v2,sum(v2) over(partition by v1 order by v2) as sum --先分组,组内在进行 1 中的操作 from wmg_test;
3、select v1,v2,sum(v2) over(partition by v1 order by v1) as sum ---稳定排序 from wmg_test;
4、select v1,v2,sum(v2) over(partition by v1) as sum --相同key的进行回填处理 from wmg_test;
5、select distinct v1,sum_01 --取一条 from ( select v1,sum(v2) over(partition by v1) as sum_01 from wmg_test ) a;
6、当然也可以逆序累加,只需order by desc 即可 总结区别:group by 和partition by的区别 group 单纯分组 partition 也能分组,但还具备累计的功能 |
CopyRight 2018-2019 实验室设备网 版权所有 |